USE [C211391_CodexOmega_DB]
GO
/****** Object:  StoredProcedure [dbo].[spVideo_GetCategories]    Script Date: 06/14/2009 08:55:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <25/03/2009>
-- Description:	<Select video categories>
-- =============================================
CREATE PROCEDURE [dbo].[spVideo_GetCategories]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM dbo.VideoCategory
END


GO
/****** Object:  StoredProcedure [dbo].[spVideo_GetTypes]    Script Date: 06/14/2009 08:55:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <25/03/2009>
-- Description:	<Select video categories>
-- =============================================
CREATE PROCEDURE [dbo].[spVideo_GetTypes]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM dbo.VideoType
END


GO
/****** Object:  StoredProcedure [dbo].[spVideoCategory_Create]    Script Date: 06/14/2009 08:55:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <27/03/2009>
-- Description:	<Create video category>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoCategory_Create] 
(
	@Definition		NVARCHAR(255),
	@RETURN_VALUE	INT OUTPUT
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO VideoCategory
	(Definition)
	VALUES 
	(@Definition)

	SET @RETURN_VALUE = SCOPE_IDENTITY() 

	RETURN @RETURN_VALUE 

END


GO
/****** Object:  StoredProcedure [dbo].[spVideoCategory_Delete]    Script Date: 06/14/2009 08:55:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <25/03/2009>
-- Description:	<Delete Video Category>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoCategory_Delete]
(
	@VideoCategoryID	INT
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DELETE FROM VideoCategory
	WHERE
	VideoCategoryID = @VideoCategoryID
	
END


GO
/****** Object:  StoredProcedure [dbo].[spVideoCategory_GetOnID]    Script Date: 06/14/2009 08:55:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <25/03/2009>
-- Description:	<Get Video Category On ID>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoCategory_GetOnID] 
(
	@VideoCategoryID	INT
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM VideoCategory 
	WHERE VideoCategoryID = @VideoCategoryID
	
END


GO
/****** Object:  StoredProcedure [dbo].[spVideoCategory_Update]    Script Date: 06/14/2009 08:55:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <25/03/2009>
-- Description:	<Update Video Category>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoCategory_Update]
(
	@VideoCategoryID	INT,
	@Definition			NVARCHAR(255)
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE VideoCategory
	SET Definition = @Definition
	WHERE
	VideoCategoryID = @VideoCategoryID
	
END


GO
/****** Object:  StoredProcedure [dbo].[spVideoLinker_Create]    Script Date: 06/14/2009 08:55:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <24/03/2009>
-- Description:	<Create Video>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoLinker_Create] 
(
	@Title				NVARCHAR(255)
	,@Description		NVARCHAR(max)
	,@Code				NVARCHAR(max)
	,@VideoCategoryID	INT
	,@VideoTypeID		INT
	,@RETURN_VALUE		INT OUTPUT
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO VideoLinker
			   (
				[Title]
			   ,[Description]
			   ,[Code]
			   ,[VideoCategoryID]
			   ,[VideoTypeID]
				)
	VALUES
				(
				 @Title
				,@Description
				,@Code
				,@VideoCategoryID
				,@VideoTypeID
				)
	
	SET @RETURN_VALUE = SCOPE_IDENTITY() 

	RETURN @RETURN_VALUE


END


GO
/****** Object:  StoredProcedure [dbo].[spVideoLinker_Delete]    Script Date: 06/14/2009 08:55:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <24/03/2009>
-- Description:	<Delete Video>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoLinker_Delete]
(
	@VideoID	INT
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DELETE FROM VideoLinker 
	WHERE
	VideoID = @VideoID

END


GO
/****** Object:  StoredProcedure [dbo].[spVideoLinker_GetList]    Script Date: 06/14/2009 08:55:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <22/03/2009>
-- Description:	<Get Videos List>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoLinker_GetList]
(
	@VideoTypeID		INT,
	@VideoCategoryID	INT,
	@Keyword			NVARCHAR(255)
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


	IF LEN(@Keyword) > 0
	BEGIN
		SELECT * FROM vwVideoLinker 
		WHERE 
		vwVideoLinker.Title LIKE '%' + @Keyword + '%'
	END
	ELSE IF @VideoTypeID > 0 AND @VideoCategoryID <= 0
	BEGIN
		SELECT * FROM vwVideoLinker 
		WHERE 
		vwVideoLinker.VideoTypeID = @VideoTypeID 
	END
	ELSE IF @VideoCategoryID > 0 AND @VideoTypeID <= 0
	BEGIN
		SELECT * FROM vwVideoLinker 
		WHERE 
		vwVideoLinker.VideoCategoryID = @VideoCategoryID 
	END
	ELSE IF @VideoTypeID > 0 AND @VideoCategoryID > 0
	BEGIN
		SELECT * FROM vwVideoLinker 
		WHERE 
		vwVideoLinker.VideoTypeID = @VideoTypeID 
		AND 
		vwVideoLinker.VideoCategoryID = @VideoCategoryID
	END
	ELSE
	BEGIN
		SELECT * FROM vwVideoLinker
	END
END


GO
/****** Object:  StoredProcedure [dbo].[spVideoLinker_GetOnID]    Script Date: 06/14/2009 08:55:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <22/03/2009>
-- Description:	<Get Video on ID>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoLinker_GetOnID]
(
	@VideoID	INT
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM vwVideoLinker WHERE VideoID = @VideoID

END


GO
/****** Object:  StoredProcedure [dbo].[spVideoLinker_Update]    Script Date: 06/14/2009 08:55:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <24/03/2009>
-- Description:	<Update Video>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoLinker_Update]
(
	 @VideoID				INT
	,@Title					NVARCHAR(255)
	,@Description			NVARCHAR(max)
	,@Code					NVARCHAR(max)
	,@VideoCategoryID		INT
	,@VideoTypeID			INT
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

UPDATE VideoLinker
   SET 
		[Title] = @Title
		,[Description] = @Description
		,[Code] = @Code
		,[VideoCategoryID] = @VideoCategoryID
		,[VideoTypeID] = @VideoTypeID
 WHERE 
		VideoID = @VideoID

END


GO
/****** Object:  StoredProcedure [dbo].[spVideoType_Create]    Script Date: 06/14/2009 08:55:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <27/03/2009>
-- Description:	<Create video type>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoType_Create] 
(
	@Definition		NVARCHAR(255),
	@RETURN_VALUE	INT OUTPUT
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO VideoType
	(Definition)
	VALUES 
	(@Definition)

	SET @RETURN_VALUE = SCOPE_IDENTITY() 

	RETURN @RETURN_VALUE

END


GO
/****** Object:  StoredProcedure [dbo].[spVideoType_Delete]    Script Date: 06/14/2009 08:55:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <25/03/2009>
-- Description:	<Delete Video Type>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoType_Delete]
(
	@VideoTypeID	INT
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DELETE FROM VideoType
	WHERE
	VideoTypeID = @VideoTypeID
	
END


GO
/****** Object:  StoredProcedure [dbo].[spVideoType_GetOnID]    Script Date: 06/14/2009 08:55:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <25/03/2009>
-- Description:	<Get Video Type On ID>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoType_GetOnID] 
(
	@VideoTypeID	INT
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM VideoType
	WHERE VideoTypeID = @VideoTypeID
	
END


GO
/****** Object:  StoredProcedure [dbo].[spVideoType_Update]    Script Date: 06/14/2009 08:55:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<CodexOmega>
-- Create date: <25/03/2009>
-- Description:	<Update Video Type>
-- =============================================
CREATE PROCEDURE [dbo].[spVideoType_Update]
(
	@VideoTypeID	INT,
	@Definition		NVARCHAR(255)
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE VideoType
	SET Definition = @Definition
	WHERE
	VideoTypeID = @VideoTypeID
	
END
